import os
import openpyxl
from openpyxl.styles import Alignment


def get_school_every_class_excel_name_generator(school_name):
    """
    根据学校名称,获取学校文件夹中每个年级,每个班级的学生成绩Excel表
    :param school_name: 学校名称
    :return: 每个班级的Excel路径
    """
    file_list = os.listdir(school_name)
    for grade_name in file_list:
        # 如果是包含"年级"的文件夹
        grade_dir_name = "{}/{}".format(school_name, grade_name)
        if os.path.isdir(grade_dir_name) \
                and "年级" in grade_name:
            # 打开文件夹
            for class_name_excel in get_class_excel_name_generator(grade_dir_name):
                yield class_name_excel


# 构造生成器函数
def get_class_excel_name_generator(dir_name):
    """
    获取班级文件夹中的每一个Excel名称并返回
    :param dir_name: 文件夹名称
    :return: Excel的路径
    """
    file_list = os.listdir(dir_name)
    # print(file_list)
    for i in file_list:
        # print(i, type(i))
        # break
        # 拼接路径
        if i.endswith(".xlsx"):
            excel_name = os.path.join(dir_name, i)
            # print(excel_name)
            # break
            yield excel_name


# 读取Excel表格中每一个数据,统计总成绩
def save_total_score(excel_name):
    """
    读取Excel表格中每一个数据,统计总成绩
    :param excel_name: Excel表格的路径
    :return: None
    """
    # excel_name = '02.xlsx'
    wb = openpyxl.load_workbook(excel_name)
    ws = wb.active
    # 最大列
    max_column = ws.max_column + 1
    # 第一行第一列
    ws.cell(1, max_column).value = '总成绩'
    # 每一行的最后一列
    for i in range(2, ws.max_row + 1):
        total_score = (
                ws.cell(i, 4).value +  # 语文
                ws.cell(i, 5).value +  # 数学
                ws.cell(i, 6).value +  # 英语
                ws.cell(i, 7).value +  # 政治
                ws.cell(i, 8).value +  # 历史
                ws.cell(i, 9).value  # 地理
        )
        ws.cell(i, max_column).value = total_score  # 赋值
    # 居中
    align_center(ws)
    # 保存
    wb.save(excel_name)
    print("{}表的总成绩统计成功".format(excel_name))


# 设置每一行每一列单元格居中
def align_center(ws):
    """
    让工作表的单元格居中
    :param ws:工作表
    """
    for i in range(1, ws.max_row + 1):  # 行
        for j in range(1, ws.max_column + 1):  # 列
            ws.cell(row=i, column=j) \
                .alignment = Alignment(horizontal='center', vertical='center')


# 执行代码
if __name__ == '__main__':
    school_name = "某某实验中学"
    # 获取该校每个年级每个班级的学生成绩Excel
    for excel in get_school_every_class_excel_name_generator(school_name):
        # 统计总成绩并保存
        save_total_score(excel)
